package com.luyuan.crm.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jxl.Cell;
import jxl.DateCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.springframework.security.providers.encoding.Md5PasswordEncoder;
import org.springframework.security.providers.encoding.PasswordEncoder;

import com.ruijie.util.NumberUtils;

/*******************************
 * 2012-7-29
 * Description:
 * </p>
 * <p>
 * Copyright: Copyright (c) 2012
 * </p>
 * 
 * @author chenqibo
 * @version 1.0
 ********************************/

public class ParseExecel2 {
	
	private static PasswordEncoder encoder = new Md5PasswordEncoder();
	
	private static final String SEPARATOR = "','";
	
	private static final String DEFAULT_PASSWORD = "88888888";
	
	private static final String ENTER = "\r\n";
	
	private static final String OUTPUT_PATH = "M:\\luyuan\\LUYUAN-UPDATE-MYSQL-9.sql";
	private static final String OUTPUT_PATH1 = "M:\\luyuan\\LUYUAN-UPDATE-MYSQL-10.sql";
	
	private static final String INPUT_PATH = "M:\\luyuan\\2008-2011.5.xls";
	
	private static List<String> users = new ArrayList<String>();
	
	/**
	 * 打印appuser member sql
	 * @param rs
	 * @throws ParseException 
	 */
	private static void printSql(Sheet rs,FileWriter file,FileWriter file1) throws ParseException{
		try {
			int rows = rs.getRows();
			SimpleDateFormat dateformat=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat t=new SimpleDateFormat("yyyyMMddHHmmss");
            DecimalFormat d = new DecimalFormat("###");
			String now = dateformat.format(new Date());
			
			int ignoreCount = 0;
			int existUse = 0;
            int dertyRow = 0;
			for(int i = 0; i < rows; ++i){
				Cell cell_username = rs.getCell(1, i);
				Cell cp = rs.getCell(2, i);
				String mobile = cp.getContents().trim();
				if (cp instanceof NumberCell) {
                    NumberCell cell = (NumberCell)cp;
                    mobile = d.format(cell.getValue());
                }
				String phone = null;
				if (mobile.contains("、")) {
                    String[] s = mobile.split("、");
                    mobile = s[0].trim();
                    phone = s[1].trim();
                } else if (mobile.contains("\n")) {
                    String[] s = mobile.split("\n");
                    if (s[0].contains("-")) {
                        mobile = s[1].trim();
                        phone = s[0].trim();
                    } else {
                    mobile = s[0].trim();
                    phone = s[1].trim();
                    }
                } 
//                else if (mobile.length() > 15) {
//                    System.out.println("--------" + mobile);
//                }
				Cell cell_postcode = rs.getCell(3, i);
				Cell cell_address = rs.getCell(4, i);
				
                //订单
                Cell createtime = rs.getCell(0, i);
                DateCell c = (DateCell)createtime;
                String cell_createtime = t.format(c.getDate());
                Cell cell_products = rs.getCell(5, i);
                Cell cell_total = rs.getCell(7, i);
                Cell cell_remarks = rs.getCell(8, i);
                Cell cell_ship = rs.getCell(9, i);
                Cell csn = rs.getCell(10, i);
                String shipNum = csn.getContents();
                if (csn instanceof NumberCell) {
                    NumberCell cell = (NumberCell)csn;
                    shipNum =  d.format(cell.getValue());
                }
                Cell cell_sale = rs.getCell(11, i);
                
                if (cell_total.getContents() == null || cell_total.getContents().trim().length() == 0 || !NumberUtils.isNumeric(cell_total.getContents())) {
                    System.out.println("cell_total.getContents()" + cell_total.getContents());
                    dertyRow ++;
                    continue;
                }
				
				//防止重复
				if(mobile!= null && mobile.trim().length() > 0 &&! "0".equals(mobile) && !users.contains("u_" + mobile) ){
				    mobile = mobile.replace("--",  "-");
				    String[] m = mobile.split(" ");
				    if (m.length !=0) {
				        boolean r = false;
                        for (int j = 0; j < m.length; j++) {
                            if (!m[j].startsWith("0")) {
                                mobile = m[j];
                                r = true;
                                break;
                            }
                        }
                        if (!r) {
                            mobile = m[0];
                        }
                    }
					//appuser
					String appuser = "INSERT INTO SCT_USER select '" + "u_" + mobile + SEPARATOR
							+ SEPARATOR + mobile + SEPARATOR + encoder.encodePassword(DEFAULT_PASSWORD, null)
							+ SEPARATOR + "Y" + SEPARATOR + "Y" + SEPARATOR + "Y" + SEPARATOR + "Y" + SEPARATOR + SEPARATOR + 
							now + SEPARATOR + "2' from DUAL where NOT EXISTS (select 1  from SCT_USER where username = '" + mobile + "');";
					//luyuan_memeber
					String member = "INSERT INTO lyt_customer(id,name,mobile,post_code,create_time,street,account_id,description,discriminator) select '" 
							+ "m_" + mobile + SEPARATOR + cell_username.getContents() + SEPARATOR + mobile 
							+ SEPARATOR + cell_postcode.getContents() + SEPARATOR + now + SEPARATOR + cell_address.getContents() 
							+ "', (select uuid from SCT_USER where username= '" + mobile + "'),'" + phone + SEPARATOR + "member' from DUAL where NOT EXISTS (select 1  from lyt_customer where mobile = '" + mobile + "');";
					//角色关系
					String role = "INSERT INTO SCT_USER_ROLE select '" + "u_" + mobile + SEPARATOR + "member' from DUAL where NOT EXISTS (select 1  from SCT_USER_ROLE left join SCT_USER on SCT_USER.uuid = SCT_USER_ROLE.USER_UUID where SCT_USER.USERNAME ='"+mobile+"');";
					
					users.add("u_" + mobile.trim());
					
					String insert_member_level = "INSERT INTO lyt_member_integral(id,member_level,total_integral,current_integral,discount) select '"
							+ "m_" + mobile + SEPARATOR + "INIT" + SEPARATOR + "0" + SEPARATOR + "0" + SEPARATOR + "1" + "' from DUAL where NOT EXISTS (select 1  from lyt_customer left join lyt_member_integral  on lyt_customer.id = lyt_member_integral.id where lyt_customer.mobile = '" + mobile + "') ;";
					
					file.write(appuser + ENTER);
					file.write(member + ENTER);
					file.write(role + ENTER);
					file.write(insert_member_level + ENTER);
				} else if (!users.contains("u_" + mobile)) {
				    ignoreCount ++;
				    System.out.println("ignore user " + cell_username.getContents() + " " + mobile);
				    continue;
				} else {
				    existUse ++;
                    System.out.println("exists user " + cell_username.getContents() + " " + mobile);
                }
				
				
				String ship = "";
				if("EMS".equals(cell_ship.getContents().trim())){
					ship = "EMS";
				}else if("顺丰".equals(cell_ship.getContents().trim()) ||
						"顺丰陆运".equals(cell_ship.getContents().trim())){
					ship = "SF_EXPRESS";
				}else if("顺丰空运".equals(cell_ship.getContents().trim())){
					ship = "SF_EXPRESS_AIR";
				}else if("申通".equals(cell_ship.getContents().trim())){
					ship = "STO_EXPRESS";
				}else if("宅急".equals(cell_ship.getContents().trim())){
                    ship = "ZJS_EXPRESS";
                }else if("圆通".equals(cell_ship.getContents().trim())){
                    ship = "YTO_EXPRESS";
                }else{
					ship = "OTHERS";
				}
					
				String sale_name = cell_sale.getContents().trim();
				if(sale_name != null && !"".equals(sale_name) && sale_name.startsWith("'")){
					sale_name = sale_name.substring(1);
				}
				
				int k = 10000 + i;
				
				String order = "INSERT INTO lyt_order(id,create_time,order_status,order_type," +
						"remarks,amount,customer_id,sale_name) VALUES('" + cell_createtime + k +SEPARATOR + dateformat.format(c.getDate()) + SEPARATOR + "RECEIVED" + SEPARATOR + "SINGLE_PRODUCT" +
						SEPARATOR + cell_remarks.getContents() + "\r\n"
						+ cell_products.getContents() + SEPARATOR + cell_total.getContents() + "', (select id from lyt_customer where mobile= '" + mobile + "' limit 1),'"
						+ sale_name + "');";

				file.write(order + ENTER);

				order = "INSERT INTO lyt_order_delivery(id,express_company,delivery_street,delivery_number," +
                "delivery_phone,delivery_post_code,receiver) VALUES('" + cell_createtime + k + SEPARATOR + ship + SEPARATOR + cell_address.getContents()
                + SEPARATOR + shipNum + SEPARATOR + mobile + SEPARATOR + 
                cell_postcode.getContents() + SEPARATOR + cell_username.getContents() + "');";
				file.write(order + ENTER);
				
				if(sale_name != null && !"".equals(sale_name)) {
                    String update_order = "UPDATE lyt_order SET saleman_id = (SELECT UUID FROM lyt_party t WHERE t.USER_NAME = '" + sale_name + "' limit 1) where id = '" +cell_createtime +  k +"';";
                    file1.write(update_order + ENTER);
                }
				
				
				String products = cell_products.getContents().trim();
				if(products != null && !"".equals(products)){
					String[] ps = products.split("],");
					int p = 0;
					for(String temp : ps){
					    if (temp.endsWith("]")) {
                            temp = temp.substring(0, temp.length() - 1);
                        }
						String[] temp1 = temp.split(",");
						if(temp1.length == 3){
						    p++;
						    
						    String tt = temp1[2].substring(1);
						    if (tt== null || tt.trim().length() == 0) {tt = "0";}
						    String ts = temp1[1];
                            if (ts== null || ts.trim().length() == 0) {ts = "1";}
							
							double total = Double.valueOf(tt) * Double.valueOf(ts);
							
							String insert_product_order = "INSERT INTO lyt_product_order(id, price,count,sub_total,order_id,product_name) VALUES('"  + cell_createtime +  k + p + SEPARATOR  + tt
									+ SEPARATOR + ts + SEPARATOR + total + SEPARATOR + cell_createtime +  k + SEPARATOR + temp1[0].substring(1) + "');";
							
//								String update_product_order = "UPDATE lyt_product_order SET product_id = (SELECT id FROM lyt_product t WHERE t.name = '"
//										+  temp1[0].substring(1) + "') WHERE id = '" + cell_createtime+  k  + p+ "';";
							
							
							file.write(insert_product_order + ENTER);
//								file.write(update_product_order + ENTER);
						}
					}
				}

                    String update_member_level = "UPDATE lyt_member_integral SET total_integral = total_integral + " + cell_total.getContents() + ",current_integral = " +
                    "current_integral + " + cell_total.getContents() + " WHERE id = '" + "m_" + mobile + "';";
                    file.write(update_member_level + ENTER);
				file.flush();
			}

            System.out.println("总记录数：" + rows);
			System.out.println("忽略用户数：" + ignoreCount);
            System.out.println("重复用户数：" + existUse);
            System.out.println("无效数据：" + dertyRow);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		InputStream is = null;
		File file = null;
		FileWriter writer = null;
		FileWriter writer1 = null;
		try {
			is = new FileInputStream(INPUT_PATH);
			Workbook wb = Workbook.getWorkbook(is);
			Sheet rs = wb.getSheet(0);
			file = new File("");
			if(file.exists()){
				file.createNewFile();
			}
			
			writer = new FileWriter(OUTPUT_PATH);
			writer1 = new FileWriter(OUTPUT_PATH1);
			
			printSql(rs,writer,writer1);
			
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}finally{
			if(is != null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			
			if(file != null){
				file = null;
			}
			
			if(writer != null){
				try {
					writer.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			
			if(writer1 != null){
				try {
					writer1.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		
	}

}
